package com.xhcl.system.service.impl;

import java.math.BigDecimal;
import java.sql.CallableStatement;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import org.apache.commons.lang3.StringUtils;
import org.apache.shiro.dao.DataAccessException;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BatchPreparedStatementSetter;
import org.springframework.jdbc.core.CallableStatementCallback;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import com.alibaba.fastjson.JSON;
import com.xhcl.system.mapper.TSrhsCmMapper;
import com.xhcl.system.mapper.TSrhsJtmxMapper;
import com.xhcl.system.domain.TSrhsCm;
import com.xhcl.system.domain.TSrhsJtmx;
import com.xhcl.system.service.ITSrhsJtmxService;
import com.xhcl.common.core.text.Convert;
import com.xhcl.common.exception.BusinessException;

/**
 * 单位项目查询Service业务层处理
 * 
 * @author xhcl
 * @date 2020-08-30
 */
@Service
public class TSrhsJtmxServiceImpl implements ITSrhsJtmxService 
{
    @Autowired
    private TSrhsJtmxMapper tSrhsJtmxMapper;
    
    @Autowired
    private JdbcTemplate jdbcTemplate;
    
    @Autowired
    private TSrhsCmMapper tSrhsCmMapper;

    /**
     * 查询单位项目查询
     * 
     * @param pztxh 单位项目查询ID
     * @return 单位项目查询
     */
    @Override
    public TSrhsJtmx selectTSrhsJtmxById(Long pztxh)
    {
        return tSrhsJtmxMapper.selectTSrhsJtmxById(pztxh);
    }

    /**
     * 查询单位项目查询列表
     * 
     * @param tSrhsJtmx 单位项目查询
     * @return 单位项目查询
     */
    @Override
    public List<TSrhsJtmx> selectTSrhsJtmxList(TSrhsJtmx tSrhsJtmx)
    {
        return tSrhsJtmxMapper.selectTSrhsJtmxList(tSrhsJtmx);
    }
    /**
     * 查询单位项目查询列表
     * 
     * @param tSrhsJtmx 单位项目查询
     * @return 单位项目查询
     */
    @Override
    public List<TSrhsJtmx> selectTSrhsJtmxFordwxmList(TSrhsJtmx tSrhsJtmx)
    {
        return tSrhsJtmxMapper.selectTSrhsJtmxFordwxmList(tSrhsJtmx);
    }
    
    public List<TSrhsJtmx> selectTSrhsJtmxLszList(TSrhsJtmx tSrhsJtmx){
    	if (tSrhsJtmx.getParams().get("fzhs")!=null && tSrhsJtmx.getParams().get("fzhs").equals("on")) {
    		 return tSrhsJtmxMapper.selectTSrhsJtmxFzlszList(tSrhsJtmx);
    	}else {
    		return tSrhsJtmxMapper.selectTSrhsJtmxPzLszList(tSrhsJtmx);
    	}
    }
    /**
     * 待查流水
     */
    public List<TSrhsJtmx> selectTSrhsDcJtmxList(TSrhsJtmx tSrhsJtmx){
    	return tSrhsJtmxMapper.selectTSrhsDcJtmxList(tSrhsJtmx);
    }
    @Transactional
    public String affirm(final String[] dclsh) throws BusinessException {
  	  List<String> Strlsh=new ArrayList<String>();
  	  for (int i=0;i<dclsh.length;i++) {
  		  String countSql="select count(*) from t_srhs_jtmx a where DFJE>0 and JFJE=0 and  a.kmdm=(select csnr from t_srhs_hsxtcs b where b.csdm='dckmdm') and SFQR='0' and SFSH='0' and DCLSH=?";
  		  int intqr=jdbcTemplate.queryForObject(countSql,Integer.class,new Object[] {dclsh[i]});
  		  if (intqr==1) {
  			  String sumSql="select to_char(sum(DFJE)) DFJE from t_srhs_jtmx a where DFJE>0 and JFJE=0 and  a.kmdm=(select csnr from t_srhs_hsxtcs b where b.csdm='dckmdm')  and SFQR='0' and SFSH='0' and DCLSH=?";

  			  String dcsr_sum=jdbcTemplate.queryForObject(sumSql,String.class,new Object[] {dclsh[i]});
  			  
  			  sumSql="select to_char(sum(AFC011)) AFC011 from T_SRHS_CM where AFC401=?";
  			  String dcsrcm_sum=jdbcTemplate.queryForObject(sumSql,String.class,new Object[] {dclsh[i]});
  			  
  			  BigDecimal a = new BigDecimal(dcsr_sum);
  			  BigDecimal b = new BigDecimal("0");
  			  if (StringUtils.isNotEmpty(dcsrcm_sum)){
  			     b = new BigDecimal(dcsrcm_sum);
  			  }
  			  if (a.compareTo(b)==0) {
  				  Strlsh.add(dclsh[i]);
  			  }
  		  }
  	  }
  	  if (Strlsh.size()==0) {
  		  List<String> Strunionlsh=new ArrayList<String>();
  		  String sql="select afc401 from T_SRHS_CM where instr(afc401,':') >0 group by afc401";
  		  List<Map<String,Object>> dccmlist=jdbcTemplate.queryForList(sql);
  		  if (dccmlist.size()>0) {
  			  for (Map<String,Object> mm:dccmlist) {
  				  String afc401=mm.get("AFC401").toString();
  				  if (StringUtils.isNotEmpty(afc401)) {
  					  for (int i=0;i<dclsh.length;i++) {
  						  if (afc401.indexOf(dclsh[i])>=0) {
  							  Strunionlsh.add(afc401);
  							  break;
  						  }
  					  }
  				  }
  			  }
  			  if (Strunionlsh.size()==0) { 
  				throw new BusinessException("未找到查明的数据");
  			  }
  			  List<String> StrTruelsh=new ArrayList<String>();
  			  for (String unionlsh:Strunionlsh) {
  				  
  				  String sumSql="select to_char(nvl(sum(AFC011),0)) AFC011 from T_SRHS_CM where AFC401=?";
  				  String dcsrcm_sum=jdbcTemplate.queryForObject(sumSql,String.class,new Object[] {unionlsh});
  				  BigDecimal a = new BigDecimal(dcsrcm_sum); 
  				  String Alsh[]=unionlsh.split("\\:");
  				  BigDecimal c = new BigDecimal(0);
  				  for (int j=0;j<Alsh.length;j++) {
  					  String sumdcSql="select to_char(nvl(sum(DFJE),0)) DFJE from t_srhs_jtmx a where DFJE>0 and JFJE=0 and  a.kmdm=(select csnr from t_srhs_hsxtcs b where b.csdm='dckmdm')  and SFQR='0' and SFSH='0' and DCLSH=?";

  					  String dcsr_sum=jdbcTemplate.queryForObject(sumdcSql,String.class,new Object[] {Alsh[j]});
  						 
  					  BigDecimal b = new BigDecimal(dcsr_sum);
  					  c=c.add(b);
  				  }
  				  //多对一金额相等
  				  if (a.compareTo(c)==0) {
  					  StrTruelsh.add(unionlsh);
  				  }
  			  }
  			  if (StrTruelsh.size()==0) {
  				throw new BusinessException("未找到查明的数据");
  			  }
  			  
  			 for (String truelsh:StrTruelsh) {
  				  String sqlguid="select to_char(systimestamp,'yyyymmddhh24missff')||sys_guid() from dual";
  				  String guid=jdbcTemplate.queryForObject(sqlguid,String.class);
  				  String insertsql="insert into t_srhs_dcqrmx (LSH, DWBM, DWMC, INCOME, YSKM, YSKMMC, REMARK, RECEIVE_DATE, QR_DATE, PZTXH, XH, XMBM, XMMC, PJH, JKR, JKYHZH, JKYHMC, SKR, SKYHZH, SKYHMC, SKYHBM,QRLSH)"
  		        		    +"select AFC401,AFA051,AFA052,AFC011,'','','',to_date(AFC015,'yyyy-MM-dd'),sysdate,'','',AFA031,AFA032,AFC001,AFC006,AFC008,AFC007,AFC002,AFC004,AFC003,AFA101,? from T_SRHS_CM where AFC401=?";
  				  jdbcTemplate.update(insertsql,new Object[] {guid,truelsh});
  				  
  				  String updatesql="update t_srhs_dcqrmx a set (YSKM, YSKMMC)=(select distinct YSKMBM, YSKMMC from T_GG_XMXX b where a.xmbm=b.xmbm) where a.LSH=? ";
  				  jdbcTemplate.update(updatesql,new Object[] {truelsh});
  				  
  				  String Alsh[]=truelsh.split("\\:");
  				  for (int j=0;j<Alsh.length;j++) {
  					  sql="update t_srhs_jtmx set SFQR='1',QRLSH=? where DFJE>0 and JFJE=0 and  kmdm=(select csnr from t_srhs_hsxtcs b where b.csdm='dckmdm') and SFQR='0' and SFSH='0' and DCLSH=?";
  					  jdbcTemplate.update(sql,new Object[] {guid,Alsh[j]}); 
  				  }
  			  }
  			  return "0";
  				 
  		  }else {
  			throw new BusinessException("未找到查明的数据");
  		  }
  	  }
  	  for (String lsh:Strlsh) {
  		  String sqlguid="select to_char(systimestamp,'yyyymmddhh24missff')||sys_guid() from dual";
  		  String guid=jdbcTemplate.queryForObject(sqlguid,String.class);
            String sql="insert into t_srhs_dcqrmx (LSH, DWBM, DWMC, INCOME, YSKM, YSKMMC, REMARK, RECEIVE_DATE, QR_DATE, PZTXH, XH, XMBM, XMMC, PJH, JKR, JKYHZH, JKYHMC, SKR, SKYHZH, SKYHMC, SKYHBM,QRLSH)"
          		    +"select AFC401,AFA051,AFA052,AFC011,'','','',to_date(AFC015,'yyyy-MM-dd'),sysdate,'','',AFA031,AFA032,AFC001,AFC006,AFC008,AFC007,AFC002,AFC004,AFC003,AFA101,? from T_SRHS_CM where AFC401=?";
  		  jdbcTemplate.update(sql,new Object[] {guid,lsh});
  		  sql="update t_srhs_dcqrmx a set (YSKM, YSKMMC)=(select distinct YSKMBM, YSKMMC from T_GG_XMXX b where a.xmbm=b.xmbm) where a.LSH=? ";
  		  jdbcTemplate.update(sql,new Object[] {lsh});
  		  sql="update t_srhs_jtmx set SFQR='1',QRLSH=? where DFJE>0 and JFJE=0 and  kmdm=(select csnr from t_srhs_hsxtcs b where b.csdm='dckmdm') and SFQR='0' and SFSH='0' and DCLSH=?";
  		  jdbcTemplate.update(sql,new Object[] {guid,lsh}); 
  	  } 
  	 return "0";
    }
    
    @Transactional
    public String comfirm(final String[] dclsh,String username) throws BusinessException{
  	  String lshs="";
  	  for (int i=0;i<dclsh.length;i++) {
  		  String countSql="select count(*) from t_srhs_jtmx a where DFJE>0 and JFJE=0 and  a.kmdm=(select csnr from t_srhs_hsxtcs b where b.csdm='dckmdm') and SFQR='1' and SFSH='0' and DCLSH=?";
  		  int intqr=jdbcTemplate.queryForObject(countSql,Integer.class,new Object[] {dclsh[i]});
  		  if (intqr!=1) { 
  			throw new BusinessException("存在未确认或已记账的记录");
  		  }
  		  if (i!=(dclsh.length-1)) {
  			  lshs=lshs+"'"+dclsh[i]+"'"+",";
  		  }else {
  			  lshs=lshs+"'"+dclsh[i]+"'";
  		  }
  	  }
  	  jdbcTemplate.execute("delete from T_SRHS_ACCOUNT_TEMP");
  	  String instSQL="insert into T_SRHS_ACCOUNT_TEMP(SJLB,ZY) values('0',?)";
  	  BatchPreparedStatementSetter setter = new BatchPreparedStatementSetter() {
          public int getBatchSize() {
            return dclsh.length;
          }
          public void setValues(PreparedStatement ps, int index) throws SQLException {
            ps.setString(1, dclsh[index]);
          }
        };
      jdbcTemplate.batchUpdate(instSQL, setter);
  	  
  	  final String pzlsh="";
  	  String sql = "{call pkg_bkpz.comfirm(?,?,?,?,?)}";
  	  Object obj = jdbcTemplate.execute(sql,   new CallableStatementCallback()
  	    {
  	      public Object doInCallableStatement(CallableStatement cs) throws SQLException, DataAccessException {
  	        cs.setString(1, pzlsh);
  	        cs.setString(2, username);
  	        cs.registerOutParameter(3, 4);
  	        cs.registerOutParameter(4, 12);
  	        cs.registerOutParameter(5, 4);
  	        cs.execute();
  	        if (!cs.getString(4).equals("ok")) {
  	        	throw new BusinessException(cs.getString(4));
  	        }
  	        return "0|" + cs.getString(4);
  	      }
  	    });
  	    return obj.toString(); 
    }
    
    
    public String affirmReset(String pztxh,String dclsh,String username,String cxyy) throws BusinessException {
  	  String countSql="select count(*) from t_srhs_jtmx a where DFJE>0 and JFJE=0 and  a.kmdm=(select csnr from t_srhs_hsxtcs b where b.csdm='dckmdm') and SFQR='0' and SFSH='0' and PZTXH=? and DCLSH=?";
  	  int intqr=jdbcTemplate.queryForObject(countSql,Integer.class,new Object[] {pztxh,dclsh});
  	  if (intqr!=1) {
  		throw new BusinessException("未查到待冲销的记录,待查记录 不存在或存在已确认记录!");
  	  }
  	  //更新冲销原因
  	  String updateSql="update t_srhs_jtmx a set JJYSKMMC=? where DFJE>0 and JFJE=0 and  a.kmdm=(select csnr from t_srhs_hsxtcs b where b.csdm='dckmdm') and SFQR='0' and SFSH='0' and PZTXH=? and DCLSH=?";
  	  jdbcTemplate.update(updateSql,new Object[] {cxyy,pztxh,dclsh});
  	  
  	  String sql = "{call pkg_bkpz.affirmReset(?,?,?,?,?,?)}";
  	  Object obj = jdbcTemplate.execute(sql, 
  	      new CallableStatementCallback()
  	    {
  	      public Object doInCallableStatement(CallableStatement cs) throws SQLException, DataAccessException {
  	        cs.setString(1, pztxh);
  	        cs.setString(2, dclsh);
  	        cs.setString(3, username);
  	        cs.registerOutParameter(4, 4);
  	        cs.registerOutParameter(5, 12);
  	        cs.registerOutParameter(6, 4);
  	        cs.execute();
  	        if (!cs.getString(5).equals("ok")) {
  	           throw new BusinessException(cs.getString(5));
  	        }
  	        return "0|" + cs.getString(5);
  	      }
  	    });
  	    return obj.toString(); 
    }
    
    public String affirmCancel(String qrlsh,String username) throws BusinessException{
  	  String countSql="select count(*) from t_srhs_jtmx a where DFJE>0 and JFJE=0 and  a.kmdm=(select csnr from t_srhs_hsxtcs b where b.csdm='dckmdm') and SFQR='1' and SFSH='1' and QRLSH=?";
  	  int intqr=jdbcTemplate.queryForObject(countSql,Integer.class,new Object[] {qrlsh});
  	  if (intqr>0) {
  		throw new BusinessException("未查到待撤销确认记录,待查确认记录 不存在或存在已记账!");
  	  }
  	  String sql="update t_srhs_jtmx a set SFQR='0',QRLSH='' where DFJE>0 and JFJE=0 and  a.kmdm=(select csnr from t_srhs_hsxtcs b where b.csdm='dckmdm') and SFQR='1' and SFSH='0' and QRLSH=? ";
  	  jdbcTemplate.update(sql,new Object[] {qrlsh});  
  	  
  	  sql="update T_DW_JKTZD set AFC009='0' where AFC001 in(select  AFC001 from t_srhs_cm a where exists(select 0 from T_SRHS_DCQRMX b where a.AFC401=b.lsh and b.QRLSH=?)) ";
  	  jdbcTemplate.update(sql,new Object[] {qrlsh});
  	  
  	  sql="delete from t_srhs_cm a where exists(select 0 from T_SRHS_DCQRMX b where a.AFC401=b.lsh and b.QRLSH=?) ";
  	  jdbcTemplate.update(sql,new Object[] {qrlsh});
  	  sql="delete from T_SRHS_DCQRMX where QRLSH=? ";
  	  jdbcTemplate.update(sql,new Object[] {qrlsh});
  	  
  	 
  	  return "0|成功";
    }
    
    public String verifyCancel(String qrlsh,String username)  throws BusinessException{
  	  String countSql="select count(*) from t_srhs_jtmx a where DFJE>0 and JFJE=0 and  a.kmdm=(select csnr from t_srhs_hsxtcs b where b.csdm='dckmdm') and SFQR='1' and SFSH='1' and QRLSH=?";
  	  int intqr=jdbcTemplate.queryForObject(countSql,Integer.class,new Object[] {qrlsh});
  	  if (intqr==0) {
  		throw new BusinessException("未查到已记账待撤销确认记录!");
  	  }
  	  String qrpzxhSql="select max(qrpztxh) qrpztxh from t_srhs_jtmx a where DFJE>0 and JFJE=0 and  a.kmdm=(select csnr from t_srhs_hsxtcs b where b.csdm='dckmdm') and SFQR='1' and SFSH='1' and QRLSH=?";
  	  final String qrpzxh=jdbcTemplate.queryForObject(qrpzxhSql,new Object[] {qrlsh},String.class).toString();
  	  if (StringUtils.isEmpty(qrpzxh)) {
  		throw new BusinessException("未查到已记账待撤销确认记录!");
  	  }
  	  String pzcountSql="select count(*) from T_SRHS_PZTO t where PZZT='1' and t.pztxh=?";
  	  int intpz=jdbcTemplate.queryForObject(pzcountSql,Integer.class,new Object[] {qrpzxh});
  	  if (intpz==0) {
  		throw new BusinessException("确认凭证已审核记账了,不能再撤销!");
  	  }
  	  String sql = "{call VOUCHER.DELVOUCHER(?,?)}";
  	  Object obj = jdbcTemplate.execute(sql, 
  	      new CallableStatementCallback()
  	    {
  	      public Object doInCallableStatement(CallableStatement cs) throws SQLException, DataAccessException {
  	        cs.setString(1, qrpzxh);
  	        cs.registerOutParameter(2, 12);
  	        cs.execute();
  			return cs.getString(2);
  	      }
  	    });
  	   if (obj!=null && (!obj.toString().equals("ok"))){
  		   throw new BusinessException("撤销已记账待查记录出错!");
  	   }
  	  String updatesql="update t_srhs_jtmx a set SFSH='0',qrpztxh='' where DFJE>0 and JFJE=0 and  a.kmdm=(select csnr from t_srhs_hsxtcs b where b.csdm='dckmdm') and SFQR='1' and SFSH='1' and QRLSH=? ";
  	  jdbcTemplate.update(updatesql,new Object[] {qrlsh});  
  	  return "0|成功";
    }
    @Transactional
    public int confirm(TSrhsCm srhscm) throws BusinessException{
    	if (!compareAmt(srhscm)){
    		throw new BusinessException("待查金额与查明金额不符!");
    	}
    	if (!compareBank(srhscm)){
    		throw new BusinessException("查明信息的收款银行与待查收入银行的不一致!");
    	}
    	int introws=insertTSrhsCm(srhscm);
    	return introws;
    }
    private boolean compareAmt(TSrhsCm srhscm)
    {
      boolean isflag = Boolean.FALSE;
      String[] lshs = srhscm.getAfc401().split("\\,");
      String itemmx = srhscm.getAfd201();
      if (StringUtils.isEmpty(itemmx)) {
        return isflag;
      }
      BigDecimal xmZje = new BigDecimal("0");
      List<Map> list = JSON.parseArray(itemmx, Map.class);
      for (Map map : list) {
        xmZje = xmZje.add(new BigDecimal(map.get("afc011").toString()));
      }
      BigDecimal dcZje = new BigDecimal("0");
      for (int i = 0; i < lshs.length; i++) {
    	  TSrhsJtmx tSrhsJtmx=new TSrhsJtmx();
    	  tSrhsJtmx.setDclsh(lshs[i]);
    	  tSrhsJtmx.setSfqr("0"); 
          List<TSrhsJtmx> alist =tSrhsJtmxMapper.selectTSrhsJtmxList(tSrhsJtmx);
          if (alist.size() > 0) {
          for (TSrhsJtmx jtmx : alist) {
            dcZje = dcZje.add(jtmx.getDfje());
          }
        }
        if (xmZje.compareTo(dcZje) == 0) {
          isflag = Boolean.TRUE;
        }
      }
      return isflag;
    }
 
    private boolean compareBank(TSrhsCm srhscm)
    {
      boolean isflag = Boolean.TRUE;
      String[] lshs = srhscm.getAfc401().split("\\,");
      for (int i = 0; i < lshs.length; i++) {
        TSrhsJtmx tSrhsJtmx=new TSrhsJtmx();
  	    tSrhsJtmx.setDclsh(lshs[i]);
  	    tSrhsJtmx.setSfqr("0"); 
        List<TSrhsJtmx> alist =tSrhsJtmxMapper.selectTSrhsJtmxList(tSrhsJtmx);
        if (alist.size() > 0) {
          for (TSrhsJtmx jtmx : alist) {
            if (!srhscm.getAfa101().equals(jtmx.getYhbm())) {
              isflag = Boolean.FALSE;
              return isflag;
            }
          }
        }
      }
      return isflag;
    }
    
    private int insertTSrhsCm(TSrhsCm srhscm) {
      int rows = 0;
      String[] lshs = srhscm.getAfc401().split("\\,");
      String itemmx = srhscm.getAfd201();
      if (StringUtils.isNotEmpty(itemmx)) {
        List<Map> list = JSON.parseArray(itemmx, Map.class);
        for (Map map : list) {
          srhscm.setAaa010("0000000000");
          srhscm.setAfc401(srhscm.getAfc401().replace(",", ":"));
          srhscm.setAfc005(srhscm.getAfc015());
          srhscm.setAfd201("");
          srhscm.setAfa031(map.get("afa031").toString());
          srhscm.setAfa032(map.get("afa032").toString());
          srhscm.setAfc011(new BigDecimal(map.get("afc011").toString()));
          this.tSrhsCmMapper.insertTSrhsCm(srhscm);
          rows++;
        }
        String qrlsh = this.tSrhsCmMapper.selectSysguid();
        srhscm.setAfc016(qrlsh);
        for (int i = 0; i < lshs.length; i++) {
          TSrhsJtmx record = new TSrhsJtmx();
          record.setSfqr("1");
          record.setQrlsh(qrlsh);
          record.getParams().put("dclsh",lshs[i]);
          record.getParams().put("sfqr","0");
          this.tSrhsJtmxMapper.updateTSrhsDcqrbzJtmx(record);
        }
        this.tSrhsCmMapper.updateDcqrmxSelective(srhscm);
        this.tSrhsCmMapper.updateDcqrmxyskmMc(srhscm);
      }
      return rows;
    }
   
}
